﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using FreeSql.Internal;
using FreeSql.Internal.Model;
using Furion;
using Furion.DatabaseAccessor;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Hosting;
using YBlog.Core;


namespace YBlog.EntityFramework.Core.FreeSqlUtils
{
    public class FreeSqlHelper
    {
        private static Type _dbContextType { get; set; }
        public static DbContext _dbContext { get; set; }
        private static IFreeSql _freeSql { get; set; }
        /// <summary>
        /// 构建FreeSql
        /// </summary>
        /// <returns></returns>
        public static IFreeSql Build()
        {
            _dbContextType = App.EffectiveTypes.FirstOrDefault(t => typeof(DbContext).IsAssignableFrom(t));
            var connectionStringPath = _dbContextType.GetCustomAttribute<AppDbContextAttribute>(true).ConnectionString;
            _freeSql = new FreeSql.FreeSqlBuilder()
            .UseConnectionString(FreeSql.DataType.MySql, App.Configuration[connectionStringPath])
            .Build();
            if (App.WebHostEnvironment.IsDevelopment())
            {
                try
                {
                    if(Boolean.Parse(App.Configuration["DatabaseSettings:AutoSyncDataStructure"] ?? "false"))
                        AutoSyncDataStructure(_freeSql);

                    if (Boolean.Parse(App.Configuration["DatabaseSettings:ReloadSeedData"] ?? "false"))
                        ReloadSeedData(_freeSql);

                }
                catch (Exception ex)
                {
                    throw;
                }

            }
            return _freeSql;
        }
        #region 同步数据库结构

        public static void AutoSyncDataStructure(IFreeSql freeSql)
        {
            Console.WriteLine($"开始同步表结构...");
            var entityCorrelationTypes = App.EffectiveTypes.Where(t => typeof(IEntity).IsAssignableFrom(t)
                && t.IsClass && !t.IsAbstract && !t.IsGenericType && !t.IsInterface && !t.IsDefined(typeof(NonAutomaticAttribute), true))
                .ToList();
            var dropColumnMap = new Dictionary<string, List<string>>();
            foreach (var entityType in entityCorrelationTypes)
            {
                var tableInfo = freeSql.CodeFirst.GetTableByEntity(entityType);
                var dbTableInfo = freeSql.DbFirst.GetTableByName(tableInfo.DbName);
                Console.WriteLine($"正在同步实体:[{entityType.Name}],表名:[{tableInfo.DbName}]");
                // 类型处理
                foreach (var property in entityType.GetProperties())
                {
                    Type ptype = property.PropertyType;
                    if (IsNullable(property.PropertyType))
                        ptype = Nullable.GetUnderlyingType(property.PropertyType);
                    // 枚举类型列处理
                    if (ptype.IsEnum) 
                        freeSql.CodeFirst.ConfigEntity(entityType, t => t.Property(property.Name).DbType("int"));
                }

                freeSql.CodeFirst.SyncStructure(entityType);
                if (dbTableInfo != null)
                {
                    var entityColumns = tableInfo.Columns;
                    foreach (var dbColumn in dbTableInfo.Columns)
                    {
                        if (entityColumns.ContainsKey(dbColumn.Name))
                        {
                            entityColumns.Remove(dbColumn.Name);
                        }
                        else
                        {
                            if (!dropColumnMap.ContainsKey(dbTableInfo.Name))
                                dropColumnMap[dbTableInfo.Name] = new List<string>();

                            dropColumnMap[dbTableInfo.Name].Add(dbColumn.Name);
                        }
                    }
                }
            }
            // 删除多余的列并修复
            Console.WriteLine("正在删除多余的列...");
            var excuteList = new List<string>();
            foreach (var tableName in dropColumnMap.Keys)
            {
                excuteList.Add(GenerateDropColumnDDL(freeSql.Ado.DataType, tableName, dropColumnMap[tableName].ToArray()));
            }
            freeSql.Ado.ExecuteNonQuery(CommandType.Text, string.Join("\r\n", excuteList));
            Console.WriteLine($"完成同步表结构...");
        }
        /// <summary>
        /// 生成删除列的SQL语句
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        private static string GenerateDropColumnDDL(FreeSql.DataType dbType, string tableName, params string[] columnNames)
        {
            var sql = new StringBuilder();
            switch (dbType)
            {
                case FreeSql.DataType.MySql:
                    sql.AppendLine($"ALTER TABLE {tableName}");
                    sql.AppendLine(string.Join(",\r\n", columnNames.Select(col => $"DROP COLUMN {col}")))
                        .Append(';');
                    break;
                case FreeSql.DataType.SqlServer:
                    sql.AppendLine($"ALTER TABLE {tableName}");
                    sql.AppendLine($"DROP ");
                    sql.Append(string.Join(",", columnNames))
                        .Append(';');
                    break;
                case FreeSql.DataType.Sqlite: // FreeSql的做法是 创建临时表，数据导进临时表，然后删除原表，将临时表改名为原表名
                    break;
                case FreeSql.DataType.Oracle:
                    sql.AppendLine($"ALTER TABLE {tableName} DROP ({string.Join(",", columnNames)})");
                    break;
            }
            return sql.ToString();
        }

        #endregion
        /// <summary>
        /// 配置种子数据
        /// </summary>
        /// <param name="build"></param>
        public static void ReloadSeedData(IFreeSql build)
        {
            Console.WriteLine($"开始加载种子数据...");
            // 加载种子配置数据
            var entityCorrelationTypes = App.EffectiveTypes.Where(t => typeof(IEntity).IsAssignableFrom(t)
                && t.IsClass && !t.IsAbstract && !t.IsGenericType && !t.IsInterface && !t.IsDefined(typeof(NonAutomaticAttribute), true))
                .ToList();
            var insertSqlList = new List<string>();
            foreach (var entitySeedDataType in entityCorrelationTypes)
            {
                var hasDataMethod = entitySeedDataType.GetMethod("HasData");
                if(hasDataMethod != null)
                {
                    build.Ado.ExecuteNonQuery(CommandType.Text, $"TRUNCATE TABLE {entitySeedDataType.GetCustomAttribute<TableAttribute>(true).Name}");
                    var instance = Activator.CreateInstance(entitySeedDataType);
                    var seedData = ((IList)hasDataMethod.Invoke(instance, new object[] { null, null })).Cast<object>().ToList();
                    insertSqlList.Add(GenerateInsertSql(entitySeedDataType, seedData, build.Ado.DataType));
                }
            }
            build.Ado.ExecuteNonQuery(CommandType.Text, string.Join("\r\n", insertSqlList));
            Console.WriteLine($"完成加载种子数据...");
        }
        public static string GenerateInsertSql(Type entitySeedDataType, List<object> data, FreeSql.DataType dbType)
        {
            var sql = new StringBuilder();
            var tableName = entitySeedDataType.GetCustomAttribute<TableAttribute>(true).Name;
            var parperties = entitySeedDataType.GetProperties();
            
            foreach(var item in data)
            {
                switch (dbType)
                {
                    case FreeSql.DataType.MySql:
                        sql.AppendLine($"INSERT INTO {tableName} ({GeneratePropertyNameJoinSql(parperties, dbType)}) VALUES ({GeneratePropertyValueJoinSql(entitySeedDataType, parperties, item,dbType)});");
                        break;
                    case FreeSql.DataType.SqlServer:
                        break;
                    case FreeSql.DataType.Sqlite:
                        break;
                    case FreeSql.DataType.Oracle:
                        break;
                }
            }
            return sql.ToString();
        }

        private static string GeneratePropertyValueJoinSql(Type entitySeedDataType, PropertyInfo[] parperties, object item, FreeSql.DataType dbType)
        {
            var result = new List<string>();
            var columns = _freeSql.DbFirst.GetTableByName(entitySeedDataType.GetCustomAttribute<TableAttribute>(true).Name).Columns;
            foreach(var property in parperties)
            {
                result.Add(FormatValueTypeMySql(columns.Find(c=>c.Name == property.Name).DbTypeText, property.GetValue(item)));
            }
            return string.Join(",", result);
        }

        public static string GeneratePropertyNameJoinSql(PropertyInfo[] parperties, FreeSql.DataType dbType)
        {
            return string.Join(",", parperties.Select(p => p.Name));
        }

        private static string FormatValueTypeMySql(string type,object value)
        {
            if (value == null)
                return "null";
            switch (type)
            {
                case "char":
                case "date":
                case "text":
                case "varchar":
                case "datetime":
                case "tinytext":
                    return $"'{value}'";
                case "int":
                    return $"{(int)(value)}";
                default:
                    return value.ToString();
            }
            throw new ArgumentNullException();
        }

        static bool IsNullable(Type type) => Nullable.GetUnderlyingType(type) != null;
    }
}
